Imports System.Data
Imports System.Data.SqlClient
Imports MyClasses
Public Class DScheme
    Public Shared Function getSchemeItem(ByVal ItemID As Integer, ByVal InvoiceDate As Date) As DataTable
        Try
            Dim strSql As String
            'Query Changed by               ....27/09/2007
            'strSql = "SELECT DISTINCT SchemeID, schemeItemID," & _
            '         " CASE WHEN schemeItemID=0 THEN 'Discount' ELSE ItemName END AS ItemName," & _
            '         " CASE WHEN schemeItemID=0 THEN discount  ELSE SchemeMinQty1 END AS SchemeMinQty1," & _
            '         " SchemeFreeQty1 , SchemeMinQty2 , " & _
            '         " SchemeFreeQty2, SchemeMinQty3, SchemeFreeQty3, discount, discounttype" & _
            '         " FROM mpItem, mScheme,tPurchaseInvoice" & _
            '         " WHERE mScheme.schemeItemID = mpItem.ItemID And mScheme.itemID= " & ItemID & _
            '         " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo"

            '"select SchemeID, schemeItemID, ItemName, SchemeMinQty1 , SchemeFreeQty1 , SchemeMinQty2 , SchemeFreeQty2, SchemeMinQty3 , SchemeFreeQty3" & _
            '" from mpItem, mScheme" & _
            '" where SchemeITemId = mpItem.ItemID And mScheme.itemID =" & ItemID

            'strSql = "SELECT DISTINCT SchemeID, schemeItemID,'Discount'  AS ItemName, " & _
            '         " CASE WHEN schemeItemID=0 THEN discount  ELSE SchemeMinQty1 END AS SchemeMinQty1," & _
            '         " SchemeFreeQty1,SchemeMinQty2,SchemeFreeQty2, SchemeMinQty3,SchemeFreeQty3, discount, discounttype" & _
            '         " FROM mpItem, mScheme " & _
            '         " WHERE mScheme.ItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
            '         " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='D'" & _
            '         " Union" & _
            '         " SELECT DISTINCT SchemeID, schemeItemID,ItemName,SchemeMinQty1,SchemeFreeQty1 ,  SchemeMinQty2 ," & _
            '         " SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3, discount, discounttype " & _
            '         " FROM mpItem, mScheme" & _
            '         " WHERE mScheme.schemeItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
            '         " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='F' order by itemname "

            '@28/01/2008 : Commented previous query & modified it for "AppliesTo" Column.
            '' ''strSql = " SELECT DISTINCT SchemeID, schemeItemID, " & _
            '' ''         " CASE WHEN discounttype='A' THEN 'Rs.' ELSE '%' END AS ItemName, " & _
            '' ''         " CASE WHEN schemeItemID=0 THEN discount  ELSE SchemeMinQty1 END AS SchemeMinQty1," & _
            '' ''         " SchemeFreeQty1,SchemeMinQty2,SchemeFreeQty2, SchemeMinQty3,SchemeFreeQty3, discount, discounttype" & _
            '' ''         " FROM mpItem, mScheme " & _
            '' ''         " WHERE mScheme.ItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
            '' ''         " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='D'" & _
            '' ''         " Union" & _
            '' ''         " SELECT DISTINCT SchemeID, schemeItemID,ItemName,SchemeMinQty1,SchemeFreeQty1 ,  SchemeMinQty2 ," & _
            '' ''         " SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3, discount, discounttype" & _
            '' ''         " FROM mpItem, mScheme" & _
            '' ''         " WHERE mScheme.schemeItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
            '' ''         " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='F' order by itemname "

            strSql = " SELECT DISTINCT SchemeID, schemeItemID, " & _
                     " CASE WHEN discounttype='A' THEN 'Rs.' ELSE '%' END AS ItemName, " & _
                     " CASE WHEN schemeItemID=0 THEN discount  ELSE SchemeMinQty1 END AS SchemeMinQty1," & _
                     " SchemeFreeQty1,SchemeMinQty2,SchemeFreeQty2, SchemeMinQty3,SchemeFreeQty3, discount, discounttype, codeDesc ,SchemeType" & _
                     " FROM mpItem, mScheme, mmisccd " & _
                     " WHERE mScheme.ItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
                     " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='D' and mmiscCd.codeid=mscheme.appliesto and mmiscCd.codetype='SCHEME'" & _
                     " Union" & _
                     " SELECT DISTINCT SchemeID, schemeItemID,ItemName,SchemeMinQty1,SchemeFreeQty1 ,  SchemeMinQty2 ," & _
                     " SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3, discount, discounttype, codeDesc ,SchemeType" & _
                     " FROM mpItem, mScheme, mmisccd" & _
                     " WHERE mScheme.schemeItemID = mpItem.ItemID And mScheme.itemID =" & ItemID & _
                     " AND '" & Format(InvoiceDate, "yyyy/MM/dd") & "'  BETWEEN ValidFrom AND ValidTo AND SchemeType='F' and mmiscCd.codeid=mscheme.appliesto and mmiscCd.codetype='SCHEME' order by itemname "
            '>>END 
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "getSchemeItem")
            Return Nothing
        End Try
    End Function
    Public Shared Function Save(ByRef scheme As CScheme, ByVal saveupdate As String) As Integer
        Dim strSql As String = ""
        Dim param(20) As SqlParameter
        Try
            If saveupdate = "S" Then
                '@20071127:Add AppliesTo field
                strSql = "INSERT INTO mscheme(SchemeID,ItemID,SchemeType,BuyerCategoryCD,SellerCategoryCD,ValidFrom," & _
                        "ValidTo,SchemeMinQty1,SchemeFreeQty1,SchemeMinQty2,SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3 ," & _
                        "DiscountType,Discount,schemeItemID,Remarks,AppliesTo,Status,LU,LD)" & _
                        "VALUES(@SchemeID,@ItemID,@SchemeType,@BuyerCategoryCD,@SellerCategoryCD,@ValidFrom," & _
                        "@ValidTo,@SchemeMinQty1,@SchemeFreeQty1,@SchemeMinQty2,@SchemeFreeQty2,@SchemeMinQty3,@SchemeFreeQty3 ," & _
                        "@DiscountType,@Discount,@schemeItemID,@Remarks,@AppliesTo,@Status,@LU,@LD)"

                scheme.SchemeID = DGeneral.GetSeqNum("SCHM")
                param(0) = New SqlParameter("@SchemeID", scheme.SchemeID)
            Else
                '@20071127:Add AppliesTo field
                strSql = "Update mscheme set  SchemeID=@SchemeID,ItemID=@ItemID,SchemeType=@SchemeType,BuyerCategoryCD=@BuyerCategoryCD,SellerCategoryCD=@SellerCategoryCD,ValidFrom=@ValidFrom," & _
                        "ValidTo=@ValidTo,SchemeMinQty1=@SchemeMinQty1,SchemeFreeQty1=@SchemeFreeQty1,SchemeMinQty2=@SchemeMinQty2,SchemeFreeQty2=@SchemeFreeQty2,SchemeMinQty3=@SchemeMinQty3,SchemeFreeQty3 =@SchemeFreeQty3 ," & _
                        "DiscountType=@DiscountType,Discount=@Discount,schemeItemID=@schemeItemID,Remarks=@Remarks,AppliesTo=@AppliesTo,Status=@Status,LU=@LU,LD=@LD  where SchemeID = @SchemeID "
                param(0) = New SqlParameter("@SchemeID", scheme.SchemeID)
            End If

            param(1) = New SqlParameter("@ItemID", scheme.ItemID)
            param(2) = New SqlParameter("@SchemeType", scheme.SchemeType)
            param(3) = New SqlParameter("@BuyerCategoryCD", 0)
            param(4) = New SqlParameter("@SellerCategoryCD", 0)
            param(5) = New SqlParameter("@ValidFrom", scheme.ValidFrom)
            param(6) = New SqlParameter("@ValidTo", scheme.ValidTo)
            param(7) = New SqlParameter("@SchemeMinQty1", scheme.SchemeMinQty1)
            param(8) = New SqlParameter("@SchemeFreeQty1", scheme.SchemeFreeQty1)
            param(9) = New SqlParameter("@SchemeMinQty2", scheme.SchemeMinQty2)
            param(10) = New SqlParameter("@SchemeFreeQty2", scheme.SchemeFreeQty2)
            param(11) = New SqlParameter("@SchemeMinQty3", scheme.SchemeMinQty3)
            param(12) = New SqlParameter("@SchemeFreeQty3", scheme.SchemeFreeQty3)
            param(13) = New SqlParameter("@DiscountType", scheme.DiscountType)
            param(14) = New SqlParameter("@Discount", scheme.Discount)
            param(15) = New SqlParameter("@schemeItemID", scheme.schemeItemID)
            param(16) = New SqlParameter("@Remarks", scheme.remarks)
            '@20071127:Add following line
            param(17) = New SqlParameter("@AppliesTo", scheme.ApllyTo)
            param(18) = New SqlParameter("@Status", "A")
            param(19) = New SqlParameter("@LU", CCommon.GetUserID())
            param(20) = New SqlParameter("@LD", Date.Now())
            DbHelper.ExecuteNonQuery(strSql, param)
            Return scheme.SchemeID
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "Dscheme", "Save")
            Return 0
        End Try
    End Function
    '          04/10/2007
    Public Shared Function GetSchemeType(ByVal SchemeID As Integer) As Char
        Dim StrSql As String
        Try
            StrSql = "SELECT DiscountType FROM mScheme WHERE SchemeID=" & SchemeID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "Dscheme", "GetSchemeType")
            Return Nothing
        End Try
    End Function
    '          05/10/2007
    Public Shared Function GetSchemeDiscount(ByVal SchemeID As Integer) As Double
        Dim StrSql As String
        Try
            StrSql = "SELECT Discount FROM mScheme WHERE SchemeID=" & SchemeID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "Dscheme", "GetSchemeDiscount")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetScheme(ByVal SchemeID As Integer) As DataTable
        Try
            Dim strSql As String
            '@20071127:Add AppliesTo field
            strSql = "SELECT SchemeID,ItemID,SchemeType,BuyerCategoryCD,SellerCategoryCD,ValidFrom," & _
                     "Validto, SchemeMinQty1,SchemeFreeQty1,SchemeMinQty2,SchemeFreeQty2,SchemeMinQty3," & _
                     "SchemeFreeQty3,DiscountType,Discount,SchemeItemId,Remarks,AppliesTo,Status FROM Mscheme " & _
                     " WHERE SchemeID=" & SchemeID
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "GetScheme")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetSchemeInfo(ByVal intSchemeId As Integer) As DataTable
        Try
            Dim strSql As String
            'strSql = "SELECT SchemeID,SchemeType,ValidFrom,ValidTo,SchemeMinQty1,SchemeFreeQty1," & _
            '         "SchemeMinQty2,SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3,DiscountType,Discount FROM MSCHEME " & _
            '         "WHERE SchemeID=" & intSchemeId & " AND getdate() between ValidFrom AND ValidTo "
            strSql = "SELECT SchemeID,SchemeType,ValidFrom,ValidTo,SchemeMinQty1,SchemeFreeQty1," & _
                     "SchemeMinQty2,SchemeFreeQty2,SchemeMinQty3,SchemeFreeQty3,DiscountType,Discount FROM MSCHEME " & _
                     "WHERE SchemeID=" & intSchemeId & " AND getdate() between ValidFrom AND ValidTo "

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "GetSchemeInfo")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetSchemeAplyTo(ByVal SchemeID As Integer, ByRef dbConnection As dbAccess) As Char
        Dim strSql As String
        Try
            strSql = "SELECT AppliesTo FROM Mscheme WHERE SchemeID=" & SchemeID
            Return DbHelper.ExecuteScalar(strSql)
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "Dscheme", "GetSchemeAplyTo")
        End Try
    End Function

    Public Shared Function IsSameItemFree(ByVal SchemeID As Integer, ByRef dbConnection As dbAccess) As Boolean
        Dim strSql As String
        Try
            strSql = "SELECT ItemID,SchemeItemID FROM Mscheme WHERE SchemeID=" & SchemeID
            Dim dtbScheme As DataTable = dbConnection.ExecuteDataTable(strSql)
            If dtbScheme.Rows.Count > 0 Then
                If dtbScheme.Rows(0).Item("ItemID") = dtbScheme.Rows(0).Item("SchemeItemID") Then
                    Return True
                Else
                    Return False
                End If
            Else
                Return False
            End If
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "Dscheme", "GetSchemeAplyTo")
        End Try
    End Function

    Public Shared Function IsSchemeUsed(ByVal SchemeID As Integer) As Integer
        Try
            If (DbHelper.ExecuteScalar("SELECT SchemeID FROM tPurchaseInvoiceDet WHERE SchemeID=" & SchemeID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT SchemeID FROM tDcDet WHERE SchemeID=" & SchemeID)) > 0 Then
                Return 1
            ElseIf (DbHelper.ExecuteScalar("SELECT SchemeID FROM tStock WHERE SchemeID=" & SchemeID)) > 0 Then
                Return 1
            Else
                Return 0
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "IsSchemeUsed")
            Return 0
        End Try
    End Function

    Public Shared Function DeleteScheme(ByVal SchemeID As Integer) As Integer
        Try
            Return DbHelper.ExecuteNonQuery(" DELETE FROM mScheme WHERE SchemeID=" & SchemeID & "")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "DeleteScheme")
            Return 0
        End Try
    End Function

    Public Shared Function IsValidScheme(ByVal SchemeID As Integer) As Boolean
        Try
            If DbHelper.ExecuteScalar("SELECT SchemeID ,DATEDIFF(D,GETDATE(),ValidTo)  FROM mScheme WHERE DATEDIFF(D,GETDATE(),ValidTo) >=0 AND SchemeID=" & SchemeID & "") > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DScheme", "DeleteScheme")
            Return False
        End Try
    End Function


End Class
